---
title: insertLetterRequest stored procedure documentation
summary: Oracle package member pk80001_legalnet.insertLetterRequest — creates a LETTER_REQUEST row and returns its newly generated sequence number.
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-31
source_schema: WCIS_DBA
source_name: pk80001_legalnet.insertLetterRequest
source_type: PROCEDURE
retrieval_status: found
retrieval_command: legends oracle query --name pk80001_legalnet
retrieval_date: 2026-08-31
---

# insertLetterRequest

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Package** | `pk80001_legalnet` |
| **Type** | PROCEDURE |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Application.Claim.Legal` |
| **Calling classes** | `clsRequestManager`, `clsScheduleManager` (via `LegalManagerBase.vb:385–410`) |

## Purpose

Creates a `LETTER_REQUEST` row for a legal case and returns its newly generated sequence number. The procedure assigns a system-generated sequence number from `letter_request_seq`, populates the row with the provided letter type, legal case number, hold date, and audit user, and returns the generated request number for downstream batch-job submission (Appworx `LGN0002_LEGALNET_LETTERS` for `RREQ` type; case-eval flag update for `DEFLT` type).

## Procedures / Methods

### insertLetterRequest

| Field | Value |
| --- | --- |
| **Direction** | — (stored procedure; no return type) |
| **Result** | Returns via IN OUT parameter `requestNum` |
| **Called by** | `clsRequestManager.vb:261` (`RREQ` — recon request); `clsScheduleManager.vb:209` (`DEFLT` — Defense Eval Form) |

**Description:** Records a request for a legal letter (e.g. acknowledgement or DEF letter) and assigns a unique sequence number (`letter_request_seq.NEXTVAL`) to track it for batch printing. The generated request number is returned via the `requestNum` IN OUT parameter and is subsequently used as the batch-job input key for Appworx letter printing.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `letterTypeCode` | IN | VARCHAR2 | — | Letter type code written to `LETTER_REQUEST.LETTER_TYPE_CODE`. Values supplied by this library: `RREQ` (recon request, on new case save when `SendReconRequest` is set), `DEFLT` (Defense Evaluation Form, from `clsScheduleManager.PrintDEF`). |
| `aLegalCaseNum` | IN | NUMBER | — | Legal case sequence number; written to `LETTER_REQUEST.legal_case_seq_num`. |
| `holdDate` | IN | DATE | — | Hold/release date written to `LETTER_REQUEST.hold_date`. May be NULL for `RREQ` type (typed NULL DbType.Date bind when `Date.MinValue` is passed by .NET). For `DEFLT`, set by caller to the next Friday if today is not Friday, or next Friday if today is Friday ([PR-ACL-006](../business-rules.md#pr-acl-006-def-letter-hold-date-friday-hold)). |
| `aClaimId` | IN | NUMBER | — | Claim ID, bound by `.NET` from `ClaimData.ClaimID`. **Accepted but never referenced in the procedure body** — see [PR-ACL-004](../business-rules.md#pr-acl-004-aclaim-id-dead-parameter). |
| `createUser` | IN | VARCHAR2 | — | Audit user written to `LETTER_REQUEST.create_user`. |
| `requestNum` | IN OUT | NUMBER | — | Letter request number. The IN value is unconditionally overwritten by `letter_request_seq.NEXTVAL`; effectively an OUT-only parameter from the caller's perspective. Returned to the .NET caller via `PortalRequest.DataRequest(0).ParmValue("requestNum")` and used as a Long in downstream batch submission or case-eval flag updates. |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-ACL-001 | Letter request numbers are server-assigned from an Oracle sequence | Always, on every invocation | `SELECT letter_request_seq.NEXTVAL INTO requestNum FROM dual` — any caller-supplied `requestNum` is discarded and replaced. |
| PR-ACL-002 | Every letter request is persisted with a system creation timestamp | Always, on every invocation | `INSERT INTO LETTER_REQUEST (...) VALUES (..., createUser, sysdate)` — `create_date` is set from `sysdate`, never from a caller value. |
| PR-ACL-003 | The generated request number is returned to the caller for downstream batch submission | After the INSERT completes | `requestNum` (IN OUT) carries the new sequence value back; `LegalManagerBase.insertLetterRequest` reads it via `PortalRequest.DataRequest(0).ParmValue("requestNum")` and returns it as `Long`. |
| PR-ACL-004 | The `aClaimId` argument has no persisted effect (dead parameter) | Always | `aClaimId` is declared but never read in the body; it is not part of the `INSERT` column list. Callers pass `ClaimData.ClaimID`, but the value is discarded. **Verified by inspection of the full procedure body.** |
| PR-ACL-005 | The local variable `v_send` is vestigial | Always | `v_send VARCHAR2(3)` is declared and assigned `'YES'` but never subsequently read. No branch depends on it. Indicates removed conditional-send logic; no current behavioural effect. |
| PR-ACL-006 | DEF letter requests are held until the next Friday | `holddate` starts at `Today` (caller-side rule, `clsScheduleManager.vb:203–209`) | If today is Friday, `holddate = Today.AddDays(7)` (next Friday); otherwise `holddate = Today.NextDayOfWeek(Friday)`. The resulting date is passed as `holdDate`. |
| PR-ACL-007 | Recon-packet requests carry no hold date | Letter type `RREQ`, new case with `SendReconRequest` set (caller-side rule, `LegalManagerBase.vb:391–395` and `clsRequestManager.vb:261`) | `Date.MinValue` is passed, which `LegalManagerBase` converts to a **typed NULL** `DbType.Date` bind, persisting `hold_date` as NULL. |
| PR-ACL-008 | The returned request number keys an asynchronous print job | Immediately after a successful `RREQ` insert (caller-side rule, `clsRequestManager.vb:262–263`) | `clsRequestAppworxJob.SubmitBatchJob(DPI, "LGN0002_LEGALNET_LETTERS", "LegalNet", …, requestNum.ToString, …)` — the Appworx batch job consumes the sequence value as its input. |

> These rules are documented in [business-rules.md](../business-rules.md). Newly introduced rules must be added to `business-rules.md` as part of the same writing task.

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| Create LETTER_REQUEST row | Inserts a new row into the `LETTER_REQUEST` table with generated sequence ID, letter type, legal case number, optional hold date, and audit user. | Every successful call to `insertLetterRequest`. |
| Assign request number from sequence | Retrieves the next value from `letter_request_seq` and assigns it to the new `LETTER_REQUEST` row and the `requestNum` OUT parameter. | Every successful call; unconditional. |
| Return request number to caller | The generated `requestNum` is returned via the IN OUT parameter and read by the .NET wrapper for downstream use. | After INSERT completes and sequence value is bound to the OUT parameter. |
| Submit Appworx print job (RREQ only) | For letter type `RREQ`, the caller (`clsRequestManager.vb:262–263`) immediately submits an Appworx batch job `LGN0002_LEGALNET_LETTERS` keyed by the returned request number, which prints recon-packet letters and updates `LEGAL_CASE.SendReconFlag = clsConstants.No`. | After `clsRequestManager.insertLetterRequest` receives a successful `RREQ` request number. |
| Update case-eval flag (DEFLT only) | For letter type `DEFLT`, the caller (`clsScheduleManager.vb:210–211` / `clsScheduleManager.PrintDEF`) sets `CurrentCase.DefSentDate = Today` and `CurrentCase.SendDefFlag = clsConstants.Yes`. | After `clsScheduleManager.insertLetterRequest` receives a successful `DEFLT` request number. |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL procedure definition</summary>

```sql
PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER)
  AS
   v_send     VARCHAR2(3);
   BEGIN

   v_send := 'YES';

  SELECT letter_request_seq.NEXTVAL
    INTO requestNum
    FROM dual;

  INSERT INTO LETTER_REQUEST
   (letter_request_Seq_num,
    LETTER_TYPE_CODE,
    legal_case_seq_num,
    hold_date,
    create_user,
    create_date)
  VALUES(requestNum,
    letterTypeCode,
    aLegalCaseNum,
    holdDate,
    createUser,
    sysdate);

  END insertLetterRequest;
```

</details>

## Calling Context (.NET)

### LegalManagerBase.insertLetterRequest

Wrapper method in `SAIF.Application.Claim.Legal\LegalManagerBase.vb:385–410`:

```vbnet
Public Function insertLetterRequest(ByVal caseSeqNum As Decimal, ByVal letterType As String,
       ByVal holdDate As Date, ByVal ClaimId As Decimal, ByVal createUser As String) As Long
    Dim requestNum As Long
    Dim PortalRequest As IPortalRequest
    Dim dr As IDataRequest = Nothing

    With DPI
        PortalRequest = .DataAccessLayer.GetPortalRequest(.PortalID)
        dr = PortalRequest.NewRequest
        dr.RequestType = DataRequestType.Scalar
        dr.CommandType = CommandType.StoredProcedure
        dr.SQLStatement = "pk80001_LEGALNET.insertLetterRequest"
        dr.AddParm("letterTypeCode", letterType, 5)
        dr.AddParm("aLegalCaseNum", caseSeqNum)
        dr.AddParm("holdDate", IIf(holdDate = Date.MinValue, Nothing, holdDate), DbType.Date)
        dr.AddParm("createUser", createUser, 30)
        dr.AddParm("aClaimId", ClaimId)
        dr.AddParm("requestNum", requestNum, ParameterDirection.InputOutput)
        dr.Execute()
        requestNum = CLng(PortalRequest.DataRequest(0).ParmValue("requestNum"))
    End With

    Return requestNum
End Function
```

### Call site 1: clsRequestManager.insertLetterRequest (RREQ)

Source: `SAIF.Application.Claim.Legal\clsRequestManager.vb:261–263`

```vbnet
Dim requestNum As Long = m_LegalManagerBase.insertLetterRequest(caseSeqNum, "RREQ", Date.MinValue, ClaimId, m_DPI.UserID)
clsRequestAppworxJob.SubmitBatchJob(DPI, "LGN0002_LEGALNET_LETTERS", "LegalNet", …, requestNum.ToString, …)
```

Letter type `RREQ` (recon request) is submitted for new case saves when `SendReconRequest` is `True`. Hold date is `Date.MinValue`, bound as typed NULL.

### Call site 2: clsScheduleManager.PrintDEF (DEFLT)

Source: `SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb:203–211`

```vbnet
' DEF letter hold date: next Friday, or a week from Friday if today is Friday
Dim holdDate As Date = If(Today.DayOfWeek = DayOfWeek.Friday, Today.AddDays(7), Today.NextDayOfWeek(Friday))
Dim requestNum As Long = m_LegalManagerBase.insertLetterRequest(caseSeqNum, "DEFLT", holdDate, ClaimId, m_DPI.UserID)
CurrentCase.DefSentDate = Today
CurrentCase.SendDefFlag = clsConstants.Yes
```

Letter type `DEFLT` (Defense Evaluation Form) is submitted from the `PrintDEF` method. Hold date is computed to the next Friday and is not NULL.

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.pk80001_legalnet` package body, `insertLetterRequest` procedure definition
- Legends CLI query executed 2026-08-31T21:20:00Z: `legends oracle query --name pk80001_legalnet`
- Calling code: `SAIF.Application.Claim.Legal\LegalManagerBase.vb:385–410`
- Call site 1: `SAIF.Application.Claim.Legal\clsRequestManager.vb:261–263`
- Call site 2: `SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb:203–211`